--/
CREATE PROCEDURE sp_getOverAll 
        @bomTop varchar(50),--母件编号
        @materiel varchar(50),--查询物料编号
        @marks varchar(50)--查询数据显示类型标记
        as
        declare @bomCode varchar(50) 
        declare @bomTopCode varchar(50)
        declare @materielCode varchar(50)
        declare @pCode varchar(50)
        declare @counts varchar(50)
        declare @bomTopVersion varchar(50)
        declare @orders int
        declare @pName varchar(50)
        declare @attritionRate varchar(50)
        declare @fixedAttritionRate    varchar(50)
        declare @processName    varchar(50)
        declare @materialProcess    varchar(50)  
        declare @rank varchar(10)
        create table #tempTable (
                       rank varchar(50), 
                       materielCode varchar(50),
                       materielName varchar(50),
                       bomTopVersion varchar(50),
                       pName varchar(50),
                       counts  varchar(50),
                       effectiveDate varchar(50),
                       expiryDate varchar(50),
                       attritionRate varchar(50),
                       fixedAttritionRate varchar(50),
                       processName varchar(50),
                       materialProcess varchar(50),
                       basisNum varchar(10),
                       auditStatusName varchar(50),
                       clienFeed varchar(50),
                       manufacturer varchar(50),
                       auditStatusCode varchar(50),
                       bomCode varchar(50),     
                       pCode varchar(50),
                       amount varchar(50),
                       mark varchar(5),
                        orders  int
                )
                 create table #singleTable (
                       rank varchar(50), 
                       materielCode varchar(50),
                       materielName varchar(50),
                       bomTopVersion varchar(50),
                       pName varchar(50),
                       counts  varchar(50),
                       effectiveDate varchar(50),
                       expiryDate varchar(50),
                       attritionRate varchar(50),
                       fixedAttritionRate varchar(50),
                       processName varchar(50),
                       materialProcess varchar(50),
                       basisNum varchar(10),
                       auditStatusName varchar(50),
                       clienFeed varchar(50),
                       manufacturer varchar(50),
                       auditStatusCode varchar(50),
                       bomCode varchar(50),     
                       pCode varchar(50),
                       amount varchar(50),
                       mark varchar(5),
                        orders  int
                )
                 create table #overAllTable (
                       rank varchar(50), 
                       materielCode varchar(50),
                       materielName varchar(50),
                       bomTopVersion varchar(50),
                       pName varchar(50),
                       counts  varchar(50),
                       effectiveDate varchar(50),
                       expiryDate varchar(50),
                       attritionRate varchar(50),
                       fixedAttritionRate varchar(50),
                       processName varchar(50),
                       materialProcess varchar(50),
                       basisNum varchar(10),
                       auditStatusName varchar(50),
                       clienFeed varchar(50),
                       manufacturer varchar(50),
                       auditStatusCode varchar(50),
                       bomCode varchar(50),     
                       pCode varchar(50),
                       amount varchar(50),
                       mark varchar(5),
                        orders  int
                )
                print 1
                
            insert into #tempTable  exec sp_getRecursiveTreeData  @bomTop,@materiel,@marks         
            declare @num int
            set @num = 0
              --循环物料信息  先去除重复
            declare c_recursive cursor for select bomCode,pCode,materielCode,bomTopVersion,counts,orders,pName,processName,materialProcess,rank  from #tempTable
             open c_recursive            
             fetch next from c_recursive into @bomCode,@pCode,@materielCode,@bomTopVersion,@counts,@orders,@pName,@processName,@materialProcess,@rank
             while(@@fetch_status = 0)
             begin
                        set @num = @num + 1
                        print @num 
                        print  @bomCode
                        declare @cc varchar(50) --存放已有物料的查询物料使用量
                        declare @bomC varchar(50)
                        --判断数据库中是否有相同bomCode 的物料数据            
                        select      @cc='', @bomC=''
                       select @cc= counts, @bomC = bomCode from #singleTable where bomCode = @bomCode     
                       print '@cc' +@cc  
                       print 'bomC====' + @bomC                                                     
                        if(@cc != '' or @cc != null)--物料已存在
                        begin
                                set @cc = cast(@counts as decimal(10,1)) + cast(@cc as decimal(10,1))
                                update #singleTable set counts = @cc where bomCode = @bomCode
                                print '已存在'                            
                        end
                        else
                        begin
                                 insert into #singleTable select * from #tempTable where bomCode = @bomCode and orders = @orders 
                        end                       
                        fetch next from c_recursive into @bomCode,@pCode,@materielCode,@bomTopVersion,@counts,@orders,@pName,@processName,@materialProcess,@rank
              end               
              print 'colse c_recursive start'       
             close c_recursive            
            deallocate c_recursive
             print 'colse c_recursive end'
             --创建游标循环已去重物料信息  插入代用物料
                declare @materielCodeS varchar(50) 
                declare @bomPCode varchar(50)
                declare @substituteCode varchar(50)
                declare @substituteName varchar(50)
                declare @effectiveDate varchar(50)
                declare @expiryDate varchar(50)
                declare @substituteTimes varchar(20)
             declare c_subS cursor for select  bomCode,pCode,materielCode,bomTopVersion,counts,orders,pName,processName,materialProcess,rank  from  #singleTable order by rank
             open c_subS
             fetch next from c_subS into @bomCode,@pCode,@materielCode,@bomTopVersion,@counts,@orders,@pName,@processName,@materialProcess,@rank
             while (@@fetch_status = 0)
             begin
                         insert into #overAllTable select * from #singleTable where bomCode = @bomCode and orders = @orders
                         --循环代用物料  插入
                         declare c_subOver cursor for select  materielCode,bomPCode,substituteCode,substituteName,effectiveDate,expiryDate,substituteTimes from b_substitute_info where status = '1' 
                           open c_subOver
                         
                        fetch next from c_subOver into @materielCodeS,@bomPCode,@substituteCode,@substituteName,@effectiveDate,@expiryDate,@substituteTimes
                        while (@@fetch_status = 0)
                        begin
                                declare @c varchar(20)
                                set @c = cast(cast(@counts as decimal(10,1)) * cast(@substituteTimes as decimal(10,1)) as decimal(10,1))
                                
                                if(@bomPCode = @pCode and @materielCode = @materielCodes)
                                begin                               
                                        insert into  #overAllTable select @rank,@substituteCode,@substituteName,@bomTopVersion,@pName,@c,@effectiveDate,@expiryDate,'','',@processName,@materialProcess,'','','','','','','','','n','' --from b_substitute_info where status = '1' and bomPCode = @bomPCode and substituteCode = @substituteCode
                                end
                                  fetch next from c_subOver  into @materielCodeS,@bomPCode,@substituteCode,@substituteName,@effectiveDate,@expiryDate,@substituteTimes
                        end
                        print 'colse c_subOver start'
                        
                        close c_subOver
                        
                        deallocate c_subOver
                         print 'colse c_subOver end'
                       fetch next from c_subS into @bomCode,@pCode,@materielCode,@bomTopVersion,@counts,@orders,@pName,@processName,@materialProcess,@rank                   
         end
         close c_subS
         deallocate c_subS
        select * from #overAllTable
/
